<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>mysql实现主从同步 | 欢迎来到guo的博客!</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="一、关于mysql主从同步在复杂的业务中，我们会对数据进行加表级锁锁定，而其它的事务无法对其进行读的操作。那么我们就会需要mysql数据库读写都能正常进行，也就是mysql的读写分离。 而实现读写分离我们需要多个数据库的数据保持一致，才能进行设置读写数据库的配置。 指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql实现主从同步">
<meta property="og:url" content="http://shangguan-lingfeng.gitee.io/blog1/2021/05/12/mysql%E5%AE%9E%E7%8E%B0%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/index.html">
<meta property="og:site_name" content="欢迎来到guo的博客!">
<meta property="og:description" content="一、关于mysql主从同步在复杂的业务中，我们会对数据进行加表级锁锁定，而其它的事务无法对其进行读的操作。那么我们就会需要mysql数据库读写都能正常进行，也就是mysql的读写分离。 而实现读写分离我们需要多个数据库的数据保持一致，才能进行设置读写数据库的配置。 指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://upload-images.jianshu.io/upload_images/5009863-865d52a5f616a52d.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/506/format/webp">
<meta property="article:published_time" content="2021-05-11T16:00:00.000Z">
<meta property="article:modified_time" content="2021-05-13T03:58:44.213Z">
<meta property="article:author" content="Jerry">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://upload-images.jianshu.io/upload_images/5009863-865d52a5f616a52d.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/506/format/webp">
  
    <link rel="alternate" href="/blog1/atom.xml" title="欢迎来到guo的博客!" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/blog1/favicon.png">
  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/typeface-source-code-pro@0.0.71/index.min.css">

  
  
<link rel="stylesheet" href="/blog1/css/style.css">

  
    
<link rel="stylesheet" href="/blog1/fancybox/jquery.fancybox.min.css">

  
<meta name="generator" content="Hexo 5.3.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/blog1/" id="logo">欢迎来到guo的博客!</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/blog1/">Home</a>
        
          <a class="main-nav-link" href="/blog1/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/blog1/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Suche"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Suche"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://shangguan-lingfeng.gitee.io/blog1"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-mysql实现主从同步" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/blog1/2021/05/12/mysql%E5%AE%9E%E7%8E%B0%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/" class="article-date">
  <time class="dt-published" datetime="2021-05-11T16:00:00.000Z" itemprop="datePublished">2021-05-12</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="p-name article-title" itemprop="headline name">
      mysql实现主从同步
    </h1>
  

      </header>
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <h3 id="一、关于mysql主从同步"><a href="#一、关于mysql主从同步" class="headerlink" title="一、关于mysql主从同步"></a>一、关于mysql主从同步</h3><p>在复杂的业务中，我们会对数据进行加表级锁锁定，而其它的事务无法对其进行读的操作。<br>那么我们就会需要mysql数据库读写都能正常进行，也就是mysql的读写分离。</p>
<p>而实现读写分离我们需要多个数据库的数据保持一致，才能进行设置读写数据库的配置。</p>
<p>指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。<br>MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。<br>主库和从库实现同步。<br><img src="https://upload-images.jianshu.io/upload_images/5009863-865d52a5f616a52d.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/506/format/webp" alt="mysql主从同步"></p>
<h3 id="二、实现主从同步"><a href="#二、实现主从同步" class="headerlink" title="二、实现主从同步"></a>二、实现主从同步</h3><h5 id="1-我们首先进入mysql数据库，开启两个服务器分别创建相同名称的数据库"><a href="#1-我们首先进入mysql数据库，开启两个服务器分别创建相同名称的数据库" class="headerlink" title="1.我们首先进入mysql数据库，开启两个服务器分别创建相同名称的数据库"></a>1.我们首先进入mysql数据库，开启两个服务器分别创建相同名称的数据库</h5><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">// 在服务器1中</span><br><span class="line">create database mydatabase charset utf8;</span><br><span class="line"></span><br><span class="line">// 在服务器2中</span><br><span class="line">create database mydatabase charset utf8;</span><br></pre></td></tr></table></figure>

<h5 id="2-然后需要对主库进行配置"><a href="#2-然后需要对主库进行配置" class="headerlink" title="2.然后需要对主库进行配置"></a>2.然后需要对主库进行配置</h5><p>进入 /etc/my.cnf文件中配置</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">log-bin=master-bin  # 指定bin-log二进制日志</span><br><span class="line">server-id=1  # 指定服务ID, 在从服务器也有, 主库和从库ID不可设置相同</span><br><span class="line">binlog-format=ROW  # bin-log格式</span><br><span class="line">binlog-do-db=mydatabase  # 指定主库</span><br></pre></td></tr></table></figure>
<p>然后重启数据库</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">systemctl restart mysqld</span><br></pre></td></tr></table></figure>
<h5 id="3-主库创建账号并授权"><a href="#3-主库创建账号并授权" class="headerlink" title="3.主库创建账号并授权"></a>3.主库创建账号并授权</h5><p>先进入mysql中</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql -u root -p</span><br><span class="line">密码：</span><br></pre></td></tr></table></figure>
<p>进入mysql中然后进入mysql库中</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">use mysql;</span><br><span class="line">// 创建用户(如果拥有该用户则不用创建)</span><br><span class="line">create user username identified by &#x27;password&#x27;;   # 我们一般不会在slave连接master中直接使用root用户，而是给他分配一个其他的账户。</span><br><span class="line"> </span><br><span class="line">// 赋予该用户复制的权利</span><br><span class="line">grant replication slave on *.* to &#x27;username&#x27;@&#x27;主机ID&#x27; identified by &#x27;password&#x27;;</span><br><span class="line"> </span><br><span class="line">// 需要flush数据库,使得配置生效</span><br><span class="line">flush privileges;</span><br></pre></td></tr></table></figure>
<h5 id="5-进入从库进行配置"><a href="#5-进入从库进行配置" class="headerlink" title="5.进入从库进行配置"></a>5.进入从库进行配置</h5><p>进入 /etc/my.cnf文件中配置</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">log-bin=master-bin  # 二进制日志的名字, 要与主库相同</span><br><span class="line">binlog-format=ROW  # 日志的格式, 也要与主库相同</span><br><span class="line">server-id=2  # 服务ID，ID与主库不能相同</span><br></pre></td></tr></table></figure>
<p>同样，从库也重启数据库</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">systemctl restart mysqld</span><br></pre></td></tr></table></figure>
<p>进入从库mysql中</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql -u root -p</span><br><span class="line">密码：</span><br></pre></td></tr></table></figure>
<p>查看<strong>主库</strong>的状态(进入mysql查看)</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show master status;</span><br></pre></td></tr></table></figure>
<p>我们会查到这张表，这张表对于我们非常有用，可以先截图记录下来</p>
<h1 id="7-在从库中设置主库的连接"><a href="#7-在从库中设置主库的连接" class="headerlink" title="7.在从库中设置主库的连接"></a>7.在从库中设置主库的连接</h1><p>同样在<strong>从库</strong>mysql中从库连接主库的操作，注意bin.000005为查看主库状态所得到的编号，每个人不一定一样</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">change master to master_host=&#x27;主库主机ID&#x27;, </span><br><span class="line">master_user=&#x27;username&#x27;, </span><br><span class="line">master_password=&#x27;password&#x27;, </span><br><span class="line">master_log_file=&#x27;master-bin.000005&#x27;, </span><br><span class="line">master_log_pos=245;</span><br></pre></td></tr></table></figure>
<p>如果报错 ERROR1198，先停止<strong>从库</strong>，再重新执行上一步命令即可</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">stop slave;</span><br></pre></td></tr></table></figure>
<p>重新开启从库</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">start slave;</span><br></pre></td></tr></table></figure>
<p>从服务器查看从库的状态</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show slave status \G</span><br></pre></td></tr></table></figure>
<p>查看一下两条线程是否为Yes,如果IO为No，先去主服务器主库编号是否发生改变，改变后则需要重新进行从库配置<br>Slave_IO_Running: Yes<br>Slave_SQL_Running: Yes</p>
<h3 id="三、实现主从同步的作用"><a href="#三、实现主从同步的作用" class="headerlink" title="三、实现主从同步的作用"></a>三、实现主从同步的作用</h3><p>1.用于数据备份, 保证数据的安全<br>主从复制可以实现从服务器作为主服务器的实时数据备份. 主服务器架构的设置, 可以大大加强mysql数据库的健壮性。<br>2.实现读写分离, 提高吞吐量<br>主从服务器架构可以通过程序或代理软件实现用户的读写分离。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://shangguan-lingfeng.gitee.io/blog1/2021/05/12/mysql%E5%AE%9E%E7%8E%B0%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/" data-id="ckomwm8uh0000cguh8prs8uqo" data-title="mysql实现主从同步" class="article-share-link">Teilen</a>
      
      
      
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/blog1/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/blog1/2021/05/12/%E5%9C%A8%E9%A1%B9%E7%9B%AE%E4%B8%AD%E8%87%AA%E5%8A%A8%E7%94%9F%E6%88%90%E6%8E%A5%E5%8F%A3%E6%96%87%E6%A1%A3/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Neuer</strong>
      <div class="article-nav-title">
        
          在项目中自动生成接口文档
        
      </div>
    </a>
  
  
    <a href="/blog1/2021/05/09/CentOS%E9%83%A8%E7%BD%B2flask%E9%A1%B9%E7%9B%AE/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Älter</strong>
      <div class="article-nav-title">nginx部署flask项目</div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/CentOS/" rel="tag">CentOS</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/Flask/" rel="tag">Flask</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/django/" rel="tag">django</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/docker/" rel="tag">docker</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/gitee/" rel="tag">gitee</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/linux/" rel="tag">linux</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/mysql/" rel="tag">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/python/" rel="tag">python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/python%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/" rel="tag">python基础知识</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/redis/" rel="tag">redis</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/vue/" rel="tag">vue</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/webshell/" rel="tag">webshell</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/%E4%BA%91%E4%B8%BB%E6%9C%BA%E5%AE%9E%E7%8E%B0%E8%A7%86%E9%A2%91%E7%9B%B4%E6%92%AD/" rel="tag">云主机实现视频直播</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog1/tags/%E5%85%B3%E4%BA%8E%E7%AC%AC%E4%B8%89%E6%96%B9/" rel="tag">关于第三方</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/blog1/tags/CentOS/" style="font-size: 17.5px;">CentOS</a> <a href="/blog1/tags/Flask/" style="font-size: 10px;">Flask</a> <a href="/blog1/tags/django/" style="font-size: 12.5px;">django</a> <a href="/blog1/tags/docker/" style="font-size: 12.5px;">docker</a> <a href="/blog1/tags/gitee/" style="font-size: 10px;">gitee</a> <a href="/blog1/tags/linux/" style="font-size: 10px;">linux</a> <a href="/blog1/tags/mysql/" style="font-size: 12.5px;">mysql</a> <a href="/blog1/tags/python/" style="font-size: 15px;">python</a> <a href="/blog1/tags/python%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/" style="font-size: 17.5px;">python基础知识</a> <a href="/blog1/tags/redis/" style="font-size: 12.5px;">redis</a> <a href="/blog1/tags/vue/" style="font-size: 20px;">vue</a> <a href="/blog1/tags/webshell/" style="font-size: 10px;">webshell</a> <a href="/blog1/tags/%E4%BA%91%E4%B8%BB%E6%9C%BA%E5%AE%9E%E7%8E%B0%E8%A7%86%E9%A2%91%E7%9B%B4%E6%92%AD/" style="font-size: 10px;">云主机实现视频直播</a> <a href="/blog1/tags/%E5%85%B3%E4%BA%8E%E7%AC%AC%E4%B8%89%E6%96%B9/" style="font-size: 12.5px;">关于第三方</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archiv</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/blog1/archives/2021/05/">五月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog1/archives/2021/04/">四月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog1/archives/2021/03/">三月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog1/archives/2021/02/">二月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog1/archives/2021/01/">一月 2021</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">letzter Beitrag</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/blog1/2021/05/14/docker+CentOS+mysql%E5%AE%9E%E7%8E%B0%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/">docker+CentOS+mysql实现主从同步</a>
          </li>
        
          <li>
            <a href="/blog1/2021/05/13/mysql%E5%AE%9E%E7%8E%B0%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB(%E5%9C%A8%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5%E5%9F%BA%E7%A1%80%E4%B8%8A)/">mysql实现读写分离</a>
          </li>
        
          <li>
            <a href="/blog1/2021/05/12/%E5%9C%A8%E9%A1%B9%E7%9B%AE%E4%B8%AD%E8%87%AA%E5%8A%A8%E7%94%9F%E6%88%90%E6%8E%A5%E5%8F%A3%E6%96%87%E6%A1%A3/">在项目中自动生成接口文档</a>
          </li>
        
          <li>
            <a href="/blog1/2021/05/12/mysql%E5%AE%9E%E7%8E%B0%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/">mysql实现主从同步</a>
          </li>
        
          <li>
            <a href="/blog1/2021/05/09/CentOS%E9%83%A8%E7%BD%B2flask%E9%A1%B9%E7%9B%AE/">nginx部署flask项目</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2021 Jerry<br>
      Powered by <a href="https://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/blog1/" class="mobile-nav-link">Home</a>
  
    <a href="/blog1/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/blog1/js/jquery-3.4.1.min.js"></script>



  
<script src="/blog1/fancybox/jquery.fancybox.min.js"></script>




<script src="/blog1/js/script.js"></script>





  </div>
</body>
</html>